﻿// -----------------------------------------------------------------------
// <copyright file="CoordinationData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db Coordination
    /// </summary>
    public class CoordinationData
    {
        /// <summary>
        /// Get Coordination By Id
        /// </summary>
        /// <param name="coordinationId">Param CoordinationId</param>
        /// <returns>Return Coordination</returns>
        public CoordinationEN GetCoordinationById(int coordinationId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CoordinationEN coordination = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationGetById, coordinationId);
                while (reader.Read())
                {
                    coordination = new CoordinationEN();

                    if (!reader.IsDBNull(0))
                    {
                        coordination.CoordinationId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        coordination.CoordinationUserId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        coordination.WorkflowProfileId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        coordination.Role = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        coordination.RW = reader.GetBoolean(4);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return coordination;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all Coordination
        /// </summary>
        /// <returns>Return list all Coordination</returns>
        public IList<CoordinationEN> GetCoordinations()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CoordinationEN> lstCoordination = new List<CoordinationEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationGetAll);
                while (reader.Read())
                {
                    CoordinationEN coordination = new CoordinationEN();

                    if (!reader.IsDBNull(0))
                    {
                        coordination.CoordinationId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        coordination.CoordinationUserId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        coordination.WorkflowProfileId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        coordination.Role = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        coordination.RW = reader.GetBoolean(4);
                    }

                    lstCoordination.Add(coordination);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCoordination;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db Coordination
        /// </summary>
        /// <param name="coordination">Paarm Coordination</param>
        /// <returns>Return Fail: 0, Success: @@Identity</returns>
        public int Insert(CoordinationEN coordination)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.CoordinationUserId, coordination.CoordinationUserId),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.WorkflowProfileId, coordination.WorkflowProfileId),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.Role, coordination.Role),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.RW, coordination.RW)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Coordination
        /// </summary>
        /// <param name="coordination">Param coordination</param>
        public void Update(CoordinationEN coordination)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.CoordinationId, coordination.CoordinationId),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.CoordinationUserId, coordination.CoordinationUserId),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.WorkflowProfileId, coordination.WorkflowProfileId),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.Role, coordination.Role),
                                    new SqlParameter(Constants.SqlParamerterName.Coordination.RW, coordination.RW)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete Coordination by coordinationId
        /// </summary>
        /// <param name="coordinationId">param coordinationId</param>
        public void Delete(int coordinationId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.Coordination.CoordinationId, coordinationId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get List Coordination by workflowProfileId
        /// </summary>
        /// <param name="workflowProfileId">Param workflowProfileId</param>
        /// <returns>Return List Coordination</returns>
        public IList<CoordinationEN> GetCoordinationByWorkflowProfileId(int workflowProfileId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CoordinationEN> lstCoordination = new List<CoordinationEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationGetByWorkflowProfileId, workflowProfileId);
                while (reader.Read())
                {
                    CoordinationEN coordination = new CoordinationEN();

                    if (!reader.IsDBNull(0))
                    {
                        coordination.CoordinationId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        coordination.CoordinationUserId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        coordination.WorkflowProfileId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        coordination.Role = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        coordination.RW = reader.GetBoolean(4);
                    }

                    lstCoordination.Add(coordination);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCoordination;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the coordination by coordination user_ RW.
        /// </summary>
        /// <param name="coordinationUserId">The coordination user id.</param>
        /// <param name="rw">if set to <c>true</c> [rw].</param>
        /// <returns>return list CoordinationEN</returns>
        public IList<CoordinationEN> GetCoordinationByCoordinationUser_RW(int coordinationUserId, bool rw)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CoordinationEN> lstCoordination = new List<CoordinationEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCoordinationGetByCoordinationUserRW, coordinationUserId, rw);
                while (reader.Read())
                {
                    CoordinationEN coordination = new CoordinationEN();

                    if (!reader.IsDBNull(0))
                    {
                        coordination.CoordinationId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        coordination.CoordinationUserId = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        coordination.WorkflowProfileId = reader.GetInt32(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        coordination.Role = reader.GetString(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        coordination.RW = reader.GetBoolean(4);
                    }

                    lstCoordination.Add(coordination);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCoordination;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the coordination by coordination user_ R w_ profile_ current.
        /// </summary>
        /// <param name="coordinationUserId">The coordination user id.</param>
        /// <param name="rw">if set to <c>true</c> [rw].</param>
        /// <param name="profileId">The profile id.</param>
        /// <param name="current">if set to <c>true</c> [current].</param>
        /// <returns>return CoordinationEN</returns>
        public CoordinationEN GetCoordinationByCoordinationUser_RW_Profile_Current(int coordinationUserId, bool rw, int profileId, bool current)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var query = from c in this.GetCoordinationByCoordinationUser_RW(coordinationUserId, rw)
                            where c.WorkflowProfile.ProfileId == profileId && c.WorkflowProfile.IsCurrent
                            select c;
                return query.FirstOrDefault();
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}